﻿using EMoney.IStock.Client.Models.CQ;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace EMoney.IStock.Client.DataAccess.CQ
{
    /// <summary>
    /// 竞猜信息数据访问类
    /// </summary>
    public class MyGuessDal
    {
        /// <summary>
        /// 新增竞猜信息
        /// </summary>
        /// <param name="myGuess"></param>
        /// <returns></returns>
        public int AddMyGuess(MyGuess myGuess)
        {
            string sql = "insert into CQ_MyGuess(UserID,TradeDate,GuessType,GuessTime,UpdateTime) values(@UserID,@TradeDate,@GuessType,getdate(),getdate())";

            SQLTools tools = new SQLTools();

            tools.AddSqlParameter("UserID", System.Data.SqlDbType.Int, myGuess.UserID);
            tools.AddSqlParameter("TradeDate", System.Data.SqlDbType.DateTime, myGuess.TradeDate);
            tools.AddSqlParameter("GuessType", System.Data.SqlDbType.Int, myGuess.GuessType);

            return SQLHelper.ExecuteNonQuery(ConnectionManager.Emfstock, System.Data.CommandType.Text, sql, tools.GetSqlParameters());
        }

        /// <summary>
        /// 更新竞猜结果
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="tradeDate"></param>
        /// <param name="guessResult">1:对 0:错</param>
        /// <returns></returns>
        public int UpdateGuessResult(MyGuess myGuess)
        {
            string sql = "update CQ_MyGuess set GuessResult=@GuessResult,IsPromotion=@IsPromotion,UpdateTime=getdate() where IsPromotion is null and UserID=@UserID and TradeDate=@TradeDate";

            SQLTools tools = new SQLTools();

            tools.AddSqlParameter("GuessResult", System.Data.SqlDbType.Int, myGuess.GuessResult);
            tools.AddSqlParameter("IsPromotion", System.Data.SqlDbType.Bit, myGuess.IsPromotion);
            tools.AddSqlParameter("UserID", System.Data.SqlDbType.Int, myGuess.UserID);
            tools.AddSqlParameter("TradeDate", System.Data.SqlDbType.DateTime, myGuess.TradeDate);

            return SQLHelper.ExecuteNonQuery(ConnectionManager.Emfstock, System.Data.CommandType.Text, sql, tools.GetSqlParameters());
        }

        /// <summary>
        /// 更新用户竞猜状态为出局
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int UpdateGuessOut(int userid)
        {
            string sql = "update CQ_MyGuess set IsPromotion=0,UpdateTime=getdate() where UserID=@UserID";

            SQLTools tools = new SQLTools();

            tools.AddSqlParameter("UserID", System.Data.SqlDbType.Int, userid);

            return SQLHelper.ExecuteNonQuery(ConnectionManager.Emfstock, System.Data.CommandType.Text, sql, tools.GetSqlParameters());
        }

        /// <summary>
        /// 获取竞猜信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="tradeDate"></param>
        /// <returns></returns>
        public MyGuess GetMyGuess(int userid, DateTime tradeDate)
        {
            string sql = "select * from CQ_MyGuess where UserID=@UserID and TradeDate=@TradeDate";

            SQLTools tools = new SQLTools();

            tools.AddSqlParameter("UserID", System.Data.SqlDbType.Int, userid);
            tools.AddSqlParameter("TradeDate", System.Data.SqlDbType.DateTime, tradeDate);

            return SQLHelper.GetDataByColumns<MyGuess>(ConnectionManager.Emfstock, sql, tools.GetSqlParameters());
        }

        /// <summary>
        /// 获取指定交易日的竞猜列表
        /// </summary>
        /// <param name="tradeDate"></param>
        /// <returns></returns>
        public List<MyGuess> GetGuessListByTradeDate(DateTime tradeDate)
        {
            string sql = "select * from CQ_MyGuess where TradeDate=@TradeDate";

            SQLTools tools = new SQLTools();

            tools.AddSqlParameter("TradeDate", System.Data.SqlDbType.DateTime, tradeDate);

            return SQLHelper.GetDataByColumnsLst<MyGuess>(ConnectionManager.Emfstock, sql, tools.GetSqlParameters());
        }

        /// <summary>
        /// 获取竞猜数量
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="result"></param>
        /// <returns></returns>
        public int GetGuessCount(int userid, int result)
        {
            SQLTools tools = new SQLTools();
            StringBuilder sql = new StringBuilder();

            sql.Append("select count(0) from CQ_MyGuess where UserID=@UserID");

            if (result == 1 || result == 0)
            {
                sql.Append(" and GuessResult=@GuessResult");

                tools.AddSqlParameter("GuessResult", System.Data.SqlDbType.Int, result);
            }

            tools.AddSqlParameter("UserID", System.Data.SqlDbType.Int, userid);

            return Convert.ToInt32(SQLHelper.ExecuteScalar(ConnectionManager.Emfstock, System.Data.CommandType.Text, sql.ToString(), tools.GetSqlParameters()));
        }

        /// <summary>
        /// 获取用户最新的竞猜信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public MyGuess GetNewestGuess(int userid)
        {
            string sql = "select top 1 * from CQ_MyGuess where UserID=@UserID order by TradeDate desc";

            SQLTools tools = new SQLTools();

            tools.AddSqlParameter("UserID", System.Data.SqlDbType.Int, userid);

            return SQLHelper.GetDataByColumns<MyGuess>(ConnectionManager.Emfstock, sql, tools.GetSqlParameters());
        }

        /// <summary>
        /// 获取总的竞猜人数
        /// </summary>
        /// <returns></returns>
        public int GetGuessUserCount()
        {
            string sql = "select COUNT(DISTINCT UserID) from CQ_MyGuess";

            return Convert.ToInt32(SQLHelper.ExecuteScalar(ConnectionManager.Emfstock, System.Data.CommandType.Text, sql, null));
        }

        /// <summary>
        /// 获取晋级人数
        /// </summary>
        /// <returns></returns>
        public int GetPromotionUserCount()
        {
            string sql = "select COUNT(DISTINCT UserID) from CQ_MyGuess where IsPromotion=1";

            return Convert.ToInt32(SQLHelper.ExecuteScalar(ConnectionManager.Emfstock, System.Data.CommandType.Text, sql, null));
        }

        /// <summary>
        /// 获取出局人数
        /// </summary>
        /// <returns></returns>
        public int GetOutUserCount()
        {
            string sql = "select COUNT(DISTINCT UserID) from CQ_MyGuess where IsPromotion=0";

            return Convert.ToInt32(SQLHelper.ExecuteScalar(ConnectionManager.Emfstock, System.Data.CommandType.Text, sql, null));
        }

        /// <summary>
        /// 获取晋级的用户编号列表
        /// </summary>
        /// <returns></returns>
        public List<int> GetPromotionUserIDList()
        {
            List<int> list = new List<int>();
            string sql = "select DISTINCT UserID from CQ_MyGuess where IsPromotion=1";

            DataTable dtResult = SQLHelper.ExecuteGetTable(ConnectionManager.Emfstock, CommandType.Text, sql, null);

            if (dtResult != null && dtResult.Rows.Count > 0)
            {
                int userid = 0;
                foreach (DataRow row in dtResult.Rows)
                {
                    if (row["UserID"] != DBNull.Value)
                    {
                        try
                        {
                            userid = Convert.ToInt32(row["UserID"]);

                            if (userid > 0 && !list.Contains(userid))
                            {
                                list.Add(userid);
                            }
                        }
                        catch (Exception ex)
                        {
                        }                        
                    }
                }
            }

            return list;
        }

        /// <summary>
        /// 获取晋级用户指定交易日的竞猜信息
        /// </summary>
        /// <param name="tradeDate"></param>
        /// <returns></returns>
        public List<MyGuess> GetPromotionUserGuessListByTradeDate(DateTime tradeDate)
        {
            string sql = "select * from CQ_MyGuess where userid in (select DISTINCT UserID from CQ_MyGuess where IsPromotion=1) and TradeDate=@TradeDate";

            SQLTools tools = new SQLTools();

            tools.AddSqlParameter("TradeDate", SqlDbType.DateTime, tradeDate);

            return SQLHelper.GetDataByColumnsLst<MyGuess>(ConnectionManager.Emfstock, sql, tools.GetSqlParameters());
        }

        /// <summary>
        /// 获取指定竞猜日胜、败数
        /// </summary>
        /// <param name="tradeDate"></param>
        /// <returns></returns>
        public DataTable GetGuessResultByTradeDate(DateTime tradeDate)
        {
            string sql = "SELECT GuessResult,COUNT(0) 'count' FROM CQ_MyGuess WHERE TradeDate=@TradeDate GROUP BY GuessResult";

            SQLTools tools = new SQLTools();

            tools.AddSqlParameter("TradeDate", System.Data.SqlDbType.DateTime, tradeDate);

            return SQLHelper.ExecuteGetTable(ConnectionManager.Emfstock, CommandType.Text, sql, tools.GetSqlParameters());
        }

        /// <summary>
        /// 获取指定竞猜日看涨、看跌数
        /// </summary>
        /// <param name="tradeDate"></param>
        /// <returns></returns>
        public DataTable GetGuessTypeByTradeDate(DateTime tradeDate)
        {
            string sql = "SELECT GuessType,COUNT(0) 'count' FROM CQ_MyGuess WHERE TradeDate=@TradeDate GROUP BY GuessType";

            SQLTools tools = new SQLTools();

            tools.AddSqlParameter("TradeDate", System.Data.SqlDbType.DateTime, tradeDate);

            return SQLHelper.ExecuteGetTable(ConnectionManager.Emfstock, CommandType.Text, sql, tools.GetSqlParameters());
        }

        public DataTable GetGuessTypeCount(DateTime tradeDate)
        {
            string sql = "SELECT GuessType,COUNT(0) as guessTypeCount FROM dbo.CQ_MyGuess WHERE TradeDate=@TradeDate GROUP BY GuessType";

            SQLTools tools = new SQLTools();

            tools.AddSqlParameter("TradeDate", SqlDbType.DateTime, tradeDate);

            return SQLHelper.ExecuteGetTable(ConnectionManager.Emfstock, CommandType.Text, sql, tools.GetSqlParameters());
        }

        public DataTable GetPromotionGuessTypeCount(DateTime tradeDate)
        {
            string sql = @"SELECT GuessType,COUNT(0) guesstypecount FROM dbo.CQ_MyGuess 
                        WHERE UserID IN (SELECT DISTINCT userid FROM dbo.CQ_MyGuess WHERE IsPromotion=1) AND TradeDate=@TradeDate GROUP BY GuessType";

            SQLTools tools = new SQLTools();

            tools.AddSqlParameter("TradeDate", SqlDbType.DateTime, tradeDate);

            return SQLHelper.ExecuteGetTable(ConnectionManager.Emfstock, CommandType.Text, sql, tools.GetSqlParameters());
        }
    }
}
